package da;

import data.Resource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.ResourceBundle;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Class ResourceDA
 * 
 * Data Accessor class of Resource.
 * 
 * @author Stijn Verslycken, Jonas Van Den Bossche
 * @date 2007-2008
 */
public class ResourceDA {

    private ResourceBundle bundle;
    private static ResourceDA instance = null;

    private ResourceDA() {
        bundle = ResourceBundle.getBundle("da.dbconsts");
    }

    public static ResourceDA getInstance() {
        if (instance == null) {
            instance = new ResourceDA();
        }
        return instance;
    }

    // <editor-fold desc="SELECTS" defaultstate="collapsed">
    
    /**
     * Returns all resources of a given village
     * @param villageId
     * @return resources
     */
    public Vector<Resource> getResourcesOfVillage(int villageId) {
        Vector<Resource> resources = null;
        Resource resource = null;

        String sql = bundle.getString("getResourcesByVillageId");
        String imagesql = bundle.getString("getResourceTypeImage");

        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setInt(1, villageId);
                ResultSet rs = prepstat.executeQuery();

                resources = new Vector<Resource>();
                while (rs.next()) {

                    resource = new Resource(rs.getInt("village_id"), rs.getString("resource_name"),
                            rs.getInt("amount"));
                    
                    // Fetch appropriate resource image
                    prepstat = dbcon.prepareStatement(imagesql);
                    prepstat.setString(1, resource.getResourceTypeName());
                    ResultSet rs2 = prepstat.executeQuery();
                    while(rs2.next()) {
                        resource.setImageUrl(rs2.getString("image_url"));
                    }
                    
                    // add resource to list
                    resources.add(resource);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(BuildingDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getResourcesOfVillage error -->" + ex.toString());
        }

        return resources;
    }

    
    /**
     * Returns a resource object of a type of resource of a village.
     * @param resourceName
     * @param villageId
     * @return resource
     */
    public Resource getResourceOfVillage(String resourceName, int villageId) {
        Resource resource = null;

        String sql = bundle.getString("getResourceByNameAndVillageId");
        String imagesql = bundle.getString("getResourceTypeImage");

        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setString(1, resourceName);
                prepstat.setInt(2, villageId);
                ResultSet rs = prepstat.executeQuery();

                while (rs.next()) {

                    new Resource(rs.getInt("village_id"), rs.getString("resource_name"),
                            rs.getInt("amount"));
                    
                    // Fetch appropriate resource image
                    prepstat = dbcon.prepareStatement(imagesql);
                    prepstat.setString(1, resource.getResourceTypeName());
                    ResultSet rs2 = prepstat.executeQuery();
                    while(rs.next()) {
                        resource.setImageUrl(rs2.getString("image_url"));
                    }


                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(BuildingDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getResourceOfVillage error -->" + ex.toString());
        }
        
        return resource;
    }

    // </editor-fold>
    
    // <editor-fold desc="INSERTS" defaultstate="collapsed">
    /**
     * Adds a resource to the DB.
     * @param resource
     */
    public void addResource(Resource resource) {
        String sql = bundle.getString("addResource");

        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Adds data to table "resource"
                prepstat.setInt(1, resource.getVillageId());
                prepstat.setString(2, resource.getResourceTypeName());
                prepstat.setInt(3, resource.getAmount());

                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(BuildingDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("addResource error -->" + ex.toString());
        }
    }

    // </editor-fold>
    // <editor-fold desc="UPDATES" defaultstate="collapsed">
    
    /**
     * Updates a resource in the DB.
     * @param resource
     */
    public void editResource(Resource resource) {
        String sql = bundle.getString("editResource");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Updates table "resource"
                prepstat.setInt(1, resource.getAmount());
                prepstat.setInt(2, resource.getVillageId());
                prepstat.setString(3, resource.getResourceTypeName());
                
                prepstat.executeUpdate();


            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(BuildingDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("editResource error -->" + ex.toString());
        }
    }
    // </editor-fold>
    // <editor-fold desc="DELETES" defaultstate="collapsed">
    
    public void removeResource(Resource resource) {
        String sql = bundle.getString("removeResource");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Deletes from table "resource"
                prepstat.setInt(1, resource.getVillageId());
                prepstat.setString(2, resource.getResourceTypeName());
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(BuildingDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("removeResource error -->" + ex.toString());
        }
    }
    // </editor-fold>
}
